package viv.karaoke.book;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class SongDataSource {

  // Database fields
  private SQLiteDatabase database;
  private mySQLiteHelper dbHelper;
  private Integer pageSize = 20;
  
  private String[] songColumns = {
		  						 mySQLiteHelper.SONG_ID,
		  						 mySQLiteHelper.SONG_NAME, 
		  						 mySQLiteHelper.SONG_ARTIST, 
		  						 mySQLiteHelper.SONG_FIRST_SENTENCE, 
		  						 mySQLiteHelper.SONG_LYRIC
		  						 };

  public SongDataSource(Context context) {
    dbHelper = new mySQLiteHelper(context);
  }

  public void open() throws SQLException {
    database = dbHelper.getWritableDatabase();
  }

  public void close() {
    dbHelper.close();
  }

  public song createSong(String songName, String artist, String firstSentence, String lyric) {
    ContentValues values = new ContentValues();
    values.put(mySQLiteHelper.SONG_NAME, songName);
    values.put(mySQLiteHelper.SONG_ARTIST, artist);
    values.put(mySQLiteHelper.SONG_FIRST_SENTENCE, firstSentence);
    values.put(mySQLiteHelper.SONG_LYRIC, lyric);
    
    long insertId = database.insert(mySQLiteHelper.TABLE_SONG, null,values);
    
    Cursor cursor = database.query(mySQLiteHelper.TABLE_SONG, songColumns, mySQLiteHelper.SONG_ID + " = " + insertId, null, null, null, null);

    song newSong = new song();
	try{
	    cursor.moveToFirst();
	    newSong = cursorToSong(cursor);
	}
	catch(Exception ex)
	{
	}
    finally
    {
    	//Make sure to close the cursor
    	cursor.close();
    }
    
    return newSong;
  }

  public void deleteSong(song song) {
    long id = song.getSongId();
    System.out.println("Song deleted with id: " + id);
    database.delete(mySQLiteHelper.TABLE_SONG, mySQLiteHelper.SONG_ID
        + " = " + id, null);
  }

  public List<song> getAllSongs(String inputValue, long last) {
    List<song> songs = new ArrayList<song>();
    /*   
    Song aSong = new Song(1, "Dong Song Lo dang", "Viet Anh", "a");
    songs.add(aSong);
    
    aSong = new Song(2, "25 minutes to late", "abc", "a");
    songs.add(aSong);
    
    aSong = new Song(3, "xyz", "sss", "a");
    songs.add(aSong);
*/
    String whereClause = mySQLiteHelper.SONG_ID + " > " + last + " LIMIT " + pageSize; 
    
    if(inputValue != "")
    {
    	whereClause = mySQLiteHelper.SONG_NAME + " like \"%" + inputValue + "%\" and " + whereClause;     	
    }
    
    Cursor cursor = database.query(mySQLiteHelper.TABLE_SONG,songColumns, 
					whereClause, null, null, null, null, null);
    
	try{
	    cursor.moveToFirst();
	    while (!cursor.isAfterLast()) {
	      song song = cursorToSong(cursor);
	      songs.add(song);
	      cursor.moveToNext();
	    }
	}
	catch(Exception ex)
	{
	}
    finally
    {
    	//Make sure to close the cursor
    	cursor.close();
    }
    return songs;
  }

public List<song> getMoreSongs(String inputValue, long last) {
    List<song> songs = new ArrayList<song>();

    String whereClause = mySQLiteHelper.SONG_ID + " > " + last + " LIMIT " + pageSize; 
    
    if(inputValue != "")
    {
    	whereClause = mySQLiteHelper.SONG_NAME + " like \"%" + inputValue + "%\" and " + whereClause;     	
    }
    
    Cursor cursor = database.query(mySQLiteHelper.TABLE_SONG, songColumns, 
									whereClause,null, null, null, null, null);			
	try{
	    cursor.moveToFirst();
	    while (!cursor.isAfterLast()) {
	      song song = cursorToSong(cursor);
	      songs.add(song);
	      cursor.moveToNext();
	    }
	}
	catch(Exception ex)
	{
	}
	finally
	{
		//Make sure to close the cursor
		cursor.close();
	}
	
	return songs;
}

  
public List<song> searchSongs(String inputValue) {
	List<song> songs = new ArrayList<song>();
	   
	Cursor cursor = database.query(mySQLiteHelper.TABLE_SONG, songColumns, 
					mySQLiteHelper.SONG_NAME + " like \"%" + inputValue + "%\"", null, null, null, null, null);

	try{
	    cursor.moveToFirst();
	    while (!cursor.isAfterLast()) {
	      song song = cursorToSong(cursor);
	      songs.add(song);
	      cursor.moveToNext();
	    }
	}
	catch(Exception ex)
	{
	}
	finally
	{
		//Make sure to close the cursor
		cursor.close();
	}
    
    return songs;
 }
  
  
private song cursorToSong(Cursor cursor) {
    song song = new song();
    song.setSongId(cursor.getLong(0));
    song.setSongName(cursor.getString(1));
    song.setArtist(cursor.getString(2));
    song.setFirstSentence(cursor.getString(3));
    song.setLyric(cursor.getString(4));
    return song;
  }
}